package com.olap.starter.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;

public class MySQLToOracle {
    public static String readTextFile(String filePath) throws IOException {
        StringBuffer sb = new StringBuffer();
        File file = new File(filePath);
        BufferedReader reader = null;
        if(file.exists()){
            try {
                reader = new BufferedReader(new FileReader(file));
                String tempStr;
                while ((tempStr = reader.readLine()) != null) {
                    sb.append(tempStr);
                }
            }catch (Exception e){
                e.printStackTrace();
                throw e;
            }finally {
                if(null != reader){
                    try{
                        reader.close();
                    }catch (IOException e){
                        e.printStackTrace();
                    }
                }
            }
        }else{
            throw new IOException("文件不存在："+filePath);
        }
        return sb.toString();
    }
    public static void main(String[] args) throws IOException {

        String tableName = "pg_plate_trial_batch_relation".trim().toUpperCase();
        String tableNameUTF = "中厚板试批与材料关系表，用于存储试批与材料之间的关联信息".trim();
        String path = "C:\\Users\\76127\\Desktop\\无标题.json";

        boolean idKey = true;

        String strings = readTextFile(path);
        JSONObject parse = JSON.parseObject(strings);
        JSONArray o = (JSONArray) parse.get("RECORDS");


        StringBuffer createBuf = new StringBuffer();
        StringBuffer comBuf = new StringBuffer();
        createBuf.append("CREATE TABLE ").append(tableName).append("(\n");
        int index = 0;
        for (Object o1 : o) {
            index++;
            JSONObject object = (JSONObject) o1;
            String COLUMN_NAME = object.get("COLUMN_NAME").toString().toUpperCase();
            String DATA_TYPE = object.get("DATA_TYPE").toString().toUpperCase();
            String COLUMN_COMMENT = object.get("COLUMN_COMMENT").toString().toUpperCase();
            Object CHARACTER_MAXIMUM_LENGTH = object.get("CHARACTER_MAXIMUM_LENGTH");
            Object COLUMN_DEFAULT = object.get("COLUMN_DEFAULT");

            createBuf.append(COLUMN_NAME).append(" ");
            if (DATA_TYPE.equals("INT") || DATA_TYPE.equals("TINYINT")) {
                DATA_TYPE = "NUMBER";
                createBuf.append(DATA_TYPE).append("(12,3)");
            } else {
                DATA_TYPE = "VARCHAR2";
                if (CHARACTER_MAXIMUM_LENGTH == null) {
                    CHARACTER_MAXIMUM_LENGTH = 50;
                }
                createBuf.append(DATA_TYPE).append("(").append(CHARACTER_MAXIMUM_LENGTH).append(")");
            }
            if (COLUMN_DEFAULT != null) {
                createBuf.append(" DEFAULT '").append(COLUMN_DEFAULT).append("'");
            }
            if(index < o.size()){
                createBuf.append(",\n");
            }else {
                if(idKey){
                    createBuf.append(",\n").append("CONSTRAINT "+tableName+" PRIMARY KEY (ID)");
                }
                createBuf.append("\n);\n");
            }
            comBuf.append("COMMENT ON COLUMN ").append(tableName).append(".").append(COLUMN_NAME).append(" IS '").append(COLUMN_COMMENT).append("';\n");
        }
        System.out.println(createBuf);


        createSEQ(tableName, tableNameUTF,idKey);

        System.out.println(comBuf);

    }

    public static void createSEQ(String tableName, String tableNameUTF,boolean idKey) {
        String com = "COMMENT ON TABLE " + tableName + " IS '" + tableNameUTF + "';\n";
        System.out.println(com);
        if(!idKey){
            return;
        }
        String s = "CREATE SEQUENCE seq_" + tableName + "_ID START WITH 1;\n";
        System.out.println(s);
        String seqMe = "CREATE OR REPLACE TRIGGER trg_" + tableName + "_ID_ins  \n" +
                "BEFORE INSERT ON " + tableName + "  \n" +
                "FOR EACH ROW  \n" +
                "WHEN (NEW.id IS NULL OR NEW.id = 0)  \n" +
                "BEGIN  \n" +
                "  SELECT seq_" + tableName + "_id.NEXTVAL  \n" +
                "  INTO :NEW.id  \n" +
                "  FROM DUAL;  \n" +
                "END;\n";
        System.out.println(seqMe);
    }
}
